<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of CJTJAction
 *
 * @author wooddoor-ljf
 */
class CJTJAction extends Action {

    private $xkArr = array("yu_wen", "shu_xue", "ying_yu", "wu_li", "hua_xue", "sheng_wu", "zheng_zhi", "li_shi", "di_li");
    private $xkArr2 = array("YuWen", "ShuXue", "YingYu", "WuLi", "HuaXue", "ShengWu", "ZhengZhi", "LiShi", "DiLi");
    private $xkMCArr = array("语文", "数学", "英语", "物理", "化学", "生物", "政治", "历史", "地理");
    private $biemingArr = array('BanBie' => '班别', 'XueHao' => '学号', 'ZuoWeiHao' => '座位号', 'ZuoWeiHao_prev' => '上次座位号', 'XingMing' => '姓名', 'ShenFenZheng' => '身份证', 'QiTa' => '其它', 'XueNian' => '学年', 'XueQi' => '学期', 'NianJi' => '年级', 'KaoShiMingCheng' => '考试名称', 'KaoShiShiJian' => '考试时间', 'BeiZhu' => '备注', 'ZongFen' => '总分', 'XiuGaiShiJian' => '修改时间',); //数据库中各列的中文名

    public function index() {
        $this->show("wooddoor's CJTJ");
    }

    public function addKS(/* $XueNian, $XueQi, $NianJi, $KaoShiMingCheng, $KaoShiShiJian, $BeiZhu */) {
        $KS = D("KaoShi"); //对应表：cjtj_kao_shi
        /* $KS->find(1);
          echo $KS->XueNian;
          echo '</br>';
          echo $KS->KaoShiMingCheng;
          echo '</br></br>'; */
        /* $data['XueNian'] = $XueNian;
          $data['XueQi'] = $XueQi;
          $data['NianJi'] = $NianJi;
          $data['KaoShiMingCheng'] = $KaoShiMingCheng;
          $data['KaoShiShiJian'] = $KaoShiShiJian;
          $data['BeiZhu'] = $BeiZhu; */
        $KS->create($_REQUEST);
        $id = $KS->add();
        //$this->show($id . "</br>" . $XueNian . "</br>" . $XueQi . "</br>" . $NianJi . "</br>" . $KaoShiMingCheng . "</br>" .$KaoShiShiJian  . "</br>" . $BeiZhu);
        //dump($id);
        //dump($data);
        $this->ajaxReturn($id);
    }

    public function getKS() {
        $KS = D("KaoShi");
        //$w = $_REQUEST;
        //unset($w["_URL_"]);
        //dump($w);
        //$w['XueQi'] = $_GET['XueQi'];
        $w = $KS->create($_REQUEST);
        $data = $KS->where($w)->select();
        //$this->show($data);
        //dump($data);
        $this->ajaxReturn($KS->select());
        //return $KS->select();
    }

    public function addXueSheng(/* $NianJi, $BanBie, $XueHao, $XingMing, $ShenFenZheng, $QiTa */) {
        $xuesheng = D('XueSheng');
        $xuesheng->create($_REQUEST);
        $id = $xuesheng->add();
        //$this->show($id . '</br>');
        //dump($id);
        $this->ajaxReturn($id);
    }

    public function getXueSheng() {
        $xuesheng = D("XueSheng");
        //$w = $_REQUEST;
        //unset($w["_URL_"]);
        $w = $xuesheng->create($_REQUEST);
        //dump($w);
        $data = $xuesheng->where($w)->select();
        $data = $xuesheng->parseFieldsMap($data);
        //$this->show($data);
        //dump($data);
        $this->ajaxReturn($data);
    }

    public function addCJ() {
        $data = $_REQUEST;
        $XueKe = D($data['XueKe']);
        unset($data["XueKe"]);
        $XueKe->create($data);
        $id = $XueKe->add();
        //$this->show($id . '</br>');
        //dump($id);
        $this->ajaxReturn($id);
    }

    public function getCJ() {
        $w = $_REQUEST;
        $XueKe = D($w['XueKe']);
        unset($w["XueKe"]);
        //unset($w["_URL_"]);
        $w = $XueKe->create($w);
        //dump($w);
        $data = $XueKe->where($w)->select();
        //$this->show($data);
        //dump($data);
        $this->ajaxReturn($data);
    }

    public function getCJ3($ksID, $xk, $xtBieMing, $bj) {
        $cj = null;// D('XueSheng');
        //$w = $_REQUEST;
        //unset($w["_URL_"]);
        //$w = $XueSheng->create($_REQUEST);
        //dump($w);
        $w['KaoShi_id'] = $ksID;
        if ($bj) {
            $w['BanBie'] = $bj;
        }
        $xkSheZhi = D('XuekeShezhi');
        $xksz = $xkSheZhi->field('SheZhi')->where(array('KaoShi_id'=>$ksID,'XueKe'=>$this->xkMCArr[$xk-1]))->find();
        $xksz = $xksz['SheZhi'];
        $cj = D('XueSheng');
        $data = $cj->setLink($this->xkArr2[$xk-1], $xksz)->field('id,KaoShi_id,BanBie as 班别,XueHao as 学号,ZuoWeiHao as 座位号,ZuoWeiHao_prev as 上次座位号,XingMing as 姓名')->where($w)->relation(true)->select();//在这里设置各字段的别名而不使用parseFieldsMap，因为使用parseFieldsMap后字段顺序可能发生变化
        //$data = $cj->parseFieldsMap($data);
        $this->ajaxReturn($data);
    }

    private function createCJSQL($xk, $xkMingcheng, $xtBieMing, $ksID, $bjSQL) {
        if ($xtBieMing) {
            $xtBieMing = str_replace("，", ",", trim($xtBieMing));
            $bm = split(",", $xtBieMing);
        }
        $strSql = "SELECT tmp.id as 学生id, tmp.BanBie as 班级, tmp.XueHao as 学号, tmp.XingMing as 姓名,
cjtj_" . $xk . ".id as " . $xkMingcheng . "id,";
        for ($i = 0; $i < count($bm); $i++) {
            $strSql = $strSql . "cjtj_" . $xk . "." . chr(ord("A") + $i) . " as " . $bm[$i] . ",";
        }
        $strSql = $strSql . "cjtj_" . $xk . ".ZongFen as 总分
FROM (SELECT * FROM cjtj_xue_sheng where (cjtj_xue_sheng.KaoShi_id=" . $ksID . $bjSQL . ") ) as tmp
left join cjtj_" . $xk . " on cjtj_" . $xk . ".XueSheng_id=tmp.id";
        return $strSql;
    }

    public function getCJ2($ksID, $xk, $xtBieMing, $bj) {
        $model = M();
        if ($bj) {
            $bjSQL = " AND cjtj_xue_sheng.BanBie=" . $bj;
        }
        if ($xk == 0) {
            $strSql = "SELECT tmp.id as 学生id, tmp.BanBie as 班级, tmp.XueHao as 学号, tmp.XingMing as 姓名,
cjtj_yu_wen.id as 语文id, cjtj_yu_wen.ZongFen as 语文,
cjtj_shu_xue.id as 数学id,cjtj_shu_xue.ZongFen as 数学,
cjtj_ying_yu.id as 英语id,cjtj_ying_yu.ZongFen as 英语,
cjtj_wu_li.id as 物理id,cjtj_wu_li.ZongFen as 物理,
cjtj_hua_xue.id as 化学id,cjtj_hua_xue.ZongFen as 化学,
cjtj_sheng_wu.id as 生物id,cjtj_sheng_wu.ZongFen as 生物,
cjtj_zheng_zhi.id as 政治id,cjtj_zheng_zhi.ZongFen as 政治,
cjtj_li_shi.id as 历史id,cjtj_li_shi.ZongFen as 历史,
cjtj_di_li.id as 地理id,cjtj_di_li.ZongFen as 地理
FROM (SELECT * FROM cjtj_xue_sheng where (cjtj_xue_sheng.KaoShi_id=" . $ksID . $bjSQL . ") ) as tmp
left join cjtj_yu_wen on cjtj_yu_wen.XueSheng_id=tmp.id
left join cjtj_shu_xue on cjtj_shu_xue.XueSheng_id=tmp.id
left join cjtj_ying_yu on cjtj_ying_yu.XueSheng_id=tmp.id
left join cjtj_wu_li on cjtj_wu_li.XueSheng_id=tmp.id
left join cjtj_hua_xue on cjtj_hua_xue.XueSheng_id=tmp.id
left join cjtj_sheng_wu on cjtj_sheng_wu.XueSheng_id=tmp.id
left join cjtj_zheng_zhi on cjtj_zheng_zhi.XueSheng_id=tmp.id
left join cjtj_li_shi on cjtj_li_shi.XueSheng_id=tmp.id
left join cjtj_di_li on cjtj_di_li.XueSheng_id=tmp.id";
        } else {
            $strSql = $this->createCJSQL($this->xkArr[$xk - 1], $this->xkMCArr[$xk - 1], $xtBieMing, $ksID, $bjSQL);
        }
        $data = $model->query($strSql);
        /*
         * SELECT cjtj_xue_sheng.BanBie,cjtj_xue_sheng.XueHao,cjtj_xue_sheng.XingMing,cjtj_yu_wen.ZongFen
          FROM cjtj_xue_sheng,cjtj_yu_wen
          WHERE (cjtj_xue_sheng.KaoShi_id = 3 AND cjtj_yu_wen.XueSheng_id = cjtj_xue_sheng.id)
         */
        /*
         * SELECT tmp.id as 学生id, tmp.BanBie as 班别, tmp.XueHao as 学号, tmp.XingMing as 姓名,
          cjtj_yu_wen.id as 语文id, cjtj_yu_wen.ZongFen as 语文, cjtj_shu_xue.id as 数学id,cjtj_shu_xue.ZongFen as 数学
          FROM (SELECT * FROM cjtj_xue_sheng where cjtj_xue_sheng.KaoShi_id=1) as tmp
          left join cjtj_yu_wen on cjtj_yu_wen.XueSheng_id=tmp.id
          left join cjtj_shu_xue on cjtj_shu_xue.XueSheng_id=tmp.id
         */
        $this->ajaxReturn($data);
    }

    public function updateCJ($xkID, $xk, $xt, $cj, $zongfen, $xueshengID) {
        $model = D($this->xkArr[$xk]);
        if ($xt) {
            $data[$xt] = $cj;
        }
        $data["ZongFen"] = $zongfen;
        $data["XiuGaiShiJian"] = time();
        if ($xkID) {
            $data["id"] = $xkID;
            $ret = $model->save($data); //这里返回的是整数型
        } else {
            $data["XueSheng_id"] = $xueshengID;
            $ret = $model->add($data); //这里返回的是文本型
        }
        $this->ajaxReturn($ret + 0); //加个0,保证返回的统一为整数型
    }

    public function deleteCJ($xueshengID) {
        $model = M("XueSheng"); //M("XueSheng")与M("xue_sheng")一致
        $data["xue_sheng"] = $model->where("id=" . $xueshengID)->delete();
        foreach ($this->xkArr as $value) {
            $xkModel = M($value);
            $data[$value] = $xkModel->where("XueSheng_id=" . $xueshengID)->delete();
        }
        $this->ajaxReturn($data);
    }

    public function readExcel($xk, $ksID) {
        vendor('PHPExcel.PHPExcel'); //载入PHPExcel
        spl_autoload_register(array('Think', 'autoload')); //重新注册autoloader类以便能够使用M或者D方法
        //http://www.jb51.net/article/29071.htm
        $PHPReader = new PHPExcel_Reader_Excel5();
        $PHPExcel = $PHPReader->load('./upload/tmp.xls');

        //读取excel文件中的第一个工作表
        $currentSheet = $PHPExcel->getSheet(0); //getSheetByName($this->xkMCArr[$xk]);
        //echo $currentSheet->getCellByColumnAndRow(0,1)->getValue();
        //取得最大的列号
        $maxColumn = ord($currentSheet->getHighestColumn()) - 65; //$currentSheet->getHighestColumn()得到的是字母列号如"A"
        //dump($maxColumn);
        for ($i = 0; $i <= $maxColumn; $i++) {
            $headers[$i] = $currentSheet->getCellByColumnAndRow($i, 1)->getValue();
        }
        if (($headers[0] != "班级") || ($headers[1] != "学号") || ($headers[2] != "姓名") || ($headers[$maxColumn] != "总分")) {
            return '第一行标题错误！';
        }
        $tableKeys = $headers;
        for ($i = 3; $i < $maxColumn; $i++) {
            $tableKeys[$i] = chr($i - 3 + 65);
        }
        //dump($headers);
        //dump($tableKeys);
        //echo 'OK';
        //return;
        //取得一共有多少行
        $allRow = $currentSheet->getHighestRow();
        // 从第3行开始输出，excel表中第一行为列名，第2行是成绩表中规定的标题行
        $retArr = NULL;
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            //获取学生信息
            $where["BanBie"] = $currentSheet->getCellByColumnAndRow(0, $currentRow)->getCalculatedValue();
            $where["XueHao"] = $currentSheet->getCellByColumnAndRow(1, $currentRow)->getCalculatedValue();
            $where["XingMing"] = $currentSheet->getCellByColumnAndRow(2, $currentRow)->getCalculatedValue();
            $where["KaoShi_id"] = $ksID;
            //读取小题分：
            for ($currentCol = 3; $currentCol < $maxColumn; $currentCol++) {
                $data[$tableKeys[$currentCol]] = $currentSheet->getCellByColumnAndRow($currentCol, $currentRow)->getCalculatedValue();
            }
            //读取总分：
            $data["ZongFen"] = $currentSheet->getCellByColumnAndRow($maxColumn, $currentRow)->getCalculatedValue();
            //dump($where);
            //dump($data);
            //首先在学生表中查找是否存在该学生：
            $model = M("XueSheng");
            $xueshengID = $model->where($where)->getField('id');
            //dump($xueshengID);
            //如果学生已经存在，则添加相应学科成绩：
            if ($xueshengID) {
                $model = M($this->xkArr[$xk]);
                //在学科表中查找该学生
                $xkID = $model->where("XueSheng_id=" . $xueshengID)->getField('id');
                //找到该生则直接更新成绩
                if ($xkID) {
                    $retArr[$currentRow - 2] = $model->where("XueSheng_id=" . $xueshengID)->save($data);
                    continue;
                }
                //没有找到该生的该科成绩，则添加该生的成绩
                $data["XueSheng_id"] = $xueshengID;
                $retArr[$currentRow - 2] = $model->add($data) + 0; //返回的是文本型，加个0以返回整数型
                continue;
            }

            //如果不存在该学生，则先添加学生
            $xueshengID = $model->add($where);
            if (!$xueshengID) {
                $retArr[$currentRow - 2] = '添加学生失败';
                continue;
            }
            //添加学生成功后再添加相应学科成绩：
            $model = M($this->xkArr[$xk]);
            $data["XueSheng_id"] = $xueshengID;
            $retArr[$currentRow - 2] = $model->add($data) + 0; //返回的是文本型，加个0以返回整数型
        }
        return $retArr;
    }

    // 上传excel成绩表格
    public function uploadExcelCJ($xk, $ksID) {
        import('ORG.Net.UploadFile');
        $upload = new UploadFile(); // 实例化上传类
        $upload->savePath = './upload/'; // 设置附件上传目录
        $upload->saveRule = "tmp"; //在保存文件时将文件名更改为“tmp”
        $upload->uploadReplace = TRUE; //覆盖原来已经上传过的同名文件
        if (!$upload->upload()) {// 上传错误提示错误信息
            echo $upload->getErrorMsg();
        } else {// 上传成功
            //echo '上传成功！';
            $this->ajaxReturn($this->readExcel($xk, $ksID));
        }
    }

}
